﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Data.SqlTypes;
using System.IO;

namespace TeamNitro.App_Code
{
    public class DBManager_Rex
    {
        public String ElectiveName { get; set; }
        public String ElectiveCode{ get; set; }
        public String CDSName { get; set; }
        public String CDSCode { get; set; }
        public String CourseName { get; set; }
        public String CourseCode { get; set; }
        public int CU { get; set; }        

        public static DataTable SelectElectiveSubject()
        {
            DataTable results = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM ElectiveSubject";
                SqlDataReader dr = comm.ExecuteReader();

                results = new DataTable();
                results.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }
        public static bool AddElectiveSubject(DBManager_Rex e)
        {
            bool results = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO ElectiveSubject (ElectiveCode, ElectiveName,CourseName, CourseCode,CU) VALUES (@ElectiveCode, @ElectiveName, @CourseName, @CourseCode, @CU)";
                comm.Parameters.AddWithValue("@ElectiveCode", e.ElectiveCode); 
                comm.Parameters.AddWithValue("@ElectiveName", e.ElectiveName);                
                comm.Parameters.AddWithValue("@CourseName", e.CourseName);
                comm.Parameters.AddWithValue("@CourseCode", e.CourseCode);
                comm.Parameters.AddWithValue("@CU", e.CU);

                SqlDataReader dr = comm.ExecuteReader();

                results = dr.Read();
                results = true;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        public static DataTable UpdateElectiveSubject(DBManager_Rex e)
        {
            DataTable results = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE ElectiveSubject SET ElectiveName = @ElectiveName, CourseName = @CourseName, CourseCode = @CourseCode, CU = @CU WHERE ElectiveCode = @ElectiveCode";
                comm.Parameters.AddWithValue("@ElectiveName", e.ElectiveName);
                comm.Parameters.AddWithValue("@ElectiveCode", e.ElectiveCode);
                comm.Parameters.AddWithValue("@CourseName", e.CourseName);
                comm.Parameters.AddWithValue("@CourseCode", e.CourseCode);
                comm.Parameters.AddWithValue("@CU", e.CU);
                
                SqlDataReader dr = comm.ExecuteReader();

                results = new DataTable();
                results.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }
        public static DataTable DeleteElectiveSubject(DBManager_Rex e)
        {
            DataTable results = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.CommandText = "DELETE FROM ElectiveSubject WHERE ElectiveCode = @ElectiveCode";
                comm.Parameters.AddWithValue("@ElectiveCode", e.ElectiveCode);
                
                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();

                results = new DataTable();
                results.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }
        public static DataTable SelectCDSSubject()
        {
            DataTable results = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();

                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM CDSSubject";
                SqlDataReader dr = comm.ExecuteReader();

                results = new DataTable();
                results.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }
        public static bool AddCDSSubject(DBManager_Rex e)
        {
            bool results = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CDSSubject (CDSCode, CDSName, CourseName, CourseCode,CU) VALUES (@CDSCode, @CDSName, @CourseName, @CourseCode, @CU)";
                comm.Parameters.AddWithValue("@CDSCode", e.CDSCode);
                comm.Parameters.AddWithValue("@CDSName", e.CDSName);
                comm.Parameters.AddWithValue("@CourseName", e.CourseName);
                comm.Parameters.AddWithValue("@CourseCode", e.CourseCode);
                comm.Parameters.AddWithValue("@CU", e.CU);

                SqlDataReader dr = comm.ExecuteReader();

                results = dr.Read();
                results = true;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        public static DataTable UpdateCDSSubject(DBManager_Rex e)
        {
            DataTable results = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE CDSSubject SET CDSName=@CDSName, CourseName=@CourseName, CourseCode=@CourseCode WHERE CDSCode = @CDSCode";
                comm.Parameters.AddWithValue("@CDSName", e.CDSName);
                comm.Parameters.AddWithValue("@CDSCode", e.CDSCode);
                comm.Parameters.AddWithValue("@CourseName", e.CourseName);
                comm.Parameters.AddWithValue("@CourseCode", e.CourseCode);
                comm.Parameters.AddWithValue("@CU", e.CU);

                SqlDataReader dr = comm.ExecuteReader();

                results = new DataTable();
                results.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }
        public static DataTable DeleteCDSSubject(DBManager_Rex e)
        {
            DataTable results = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.CommandText = "DELETE FROM CDSSubject WHERE CDSCode = @CDSCode";
                comm.Parameters.AddWithValue("@CDSCode", e.CDSCode);

                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();

                results = new DataTable();
                results.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

    
        public static bool AdminLogin(String Username, String Password)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";
            
                try
                {
                    conn.Open();

                    SqlCommand comm = new SqlCommand();
                    comm.CommandText = "SELECT AdminID, Password FROM Admin WHERE AdminID=@AdminID AND Password=@Password";
                    comm.Parameters.AddWithValue("@AdminID", Username);
                    comm.Parameters.AddWithValue("@Password", Password);
                    comm.Connection = conn;

                    SqlDataReader dr = comm.ExecuteReader();
                    
                    result = dr.Read();


                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();                    
                }
                return result;
            }
        public static bool StudentLogin(String Username, String Password)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";
            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT StudentID, Password FROM Student WHERE StudentID=@StudentID AND Password=@Password";
                comm.Parameters.AddWithValue("@StudentID", Username);
                comm.Parameters.AddWithValue("@Password", Password);
                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();

               
                result = dr.Read();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
                
        }
        public static bool StaffLogin(String Username, String Password)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT StaffID, Password FROM Staff WHERE StaffID=@StaffID AND Password=@Password";
                comm.Parameters.AddWithValue("@StaffID", Username);
                comm.Parameters.AddWithValue("@Password", Password);
                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();

                result = true;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        
        
        public static bool ResetPassword(String Username, String Password)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=.;Initial Catalog=subjectdb;Integrated Security=true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.CommandText = "UPDATE Student SET Password=@Password WHERE StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", Username);
                comm.Parameters.AddWithValue("@Password", Password);
                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();
                result = true;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        public static bool RetrieveUsername(String Username)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=.;Initial Catalog=subjectdb;Integrated Security=true";

            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT StudentID FROM Student";
                comm.Parameters.AddWithValue("@StudentID", Username);
                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();
                result = true;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static String RetrieveEmail(String Username)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=.;Initial Catalog=subjectdb;Integrated Security=true";
            String email = null;
            try
            {
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT Email from Student Where StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", Username);
                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    email = (String)dr["Email"];
                }
                
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return email;
        }
    }
    
}
